'  ==========================================================================
'  |                                                                        |
'  | SQLitening Example C                                                   |
'  |                                                                        |
'  ==========================================================================
'  This is example is an attempt to do most of the things a complete application
'  would do.  It will do the following:
'     1. Create a main database with four tables: Customers, Parts, Orders,
'        and LineItems.
'     2. Insert records into Customers and Parts.
'     3. Create some Orders.
'     4. Query some Customers, Order, and Parts using joins and non joins.
'     5. Update some Orders and Parts.
'     7. Create an archive database for old Orders and attach it.
'     8. Move some Orders from main database to archive.
'     9. Update and Query using Compress and/or Encrypt with Bind.
'    10. Query using an array for the returning record set.
'    11. Query using a string for the returning record set.
'    12. Copy database (backup).
'
'  Each SQLitening command will begin with "' ==========>>> SQLitening" followed
'  by comments about each command.
'  ==========================================================================
'  PUBLIC DOMAIN SOFTWARE
'  The author or authors of this code dedicate any and all
'  copyright interest in this code to the public domain.
'  Anyone is free to copy, modify, publish, use, compile,
'  sell, or distribute the original code, either in source
'  code form or as a compiled binary, for any purpose,
'  commercial or non-commercial, and by any means.
'  Fred Meier - July 2010
'  ==========================================================================

#Dim All
#Compile Exe "..\Bin\ExampleC.Exe"
#Include "..\Inc\SQLitening.Inc"

'============================<[ Equates ]>=============================

' ==========>>> Misc
%True = -1
%False = 0
%IDCANCEL = 2
%IDYES = 6
%IDNO = 7
%MB_YESNO = &H00000004&
%MB_YESNOCANCEL = &H00000003&

' ==========>>> Set Numbers
%snCustomers   = 1
%snParts       = 2
%snOrders      = 3
%snLineItems   = 4
%snJoin        = 5

'=========================<[ Misc Declares ]>==========================
Declare Sub      ExitProcess LIB "KERNEL32.DLL" Alias "ExitProcess" (byval uExitCode as Long)

'============================<[ Globals ]>=============================
Global gsTitle as String

'=========================<[ Copy Database ]>==========================
Sub CopyDatabase
' Will copy the current Main and Archive database files to .Bak files.

   ' ==========>>> SQLitening -- Copy Database
   ' Will copy (make backup) of the Main database.
   slCopyDatabase "ExampleC_Main.Bak"

   ' ==========>>> SQLitening -- Copy Database
   ' Will copy (make backup) of the Archive database.
   slCopyDatabase "ExampleC_Archive.Bak", "", "Archive"

End Sub

'==========================<[ Query String ]>==========================
Sub QueryString
' Will select all the columns for all the customers returning the record
' set in a delimited string.  Each field and record is delimited by a single
' character.  We will used the default field delimiters ofa $BS and the default
' record delimiter of $VT.  These defaults can be change using the Q ModChar.
' Once we have the string we will display the data going forward thru the string
' and then display the same data going backward.

   Local llColIX, llRowIX as Long
   Local lsA as String
   Local lsRows, lsRow as String
   Local lsMsgData as String

   ' ==========>>> SQLitening -- Sel Str
   ' Get all columns for all customers into a string.  Note
   ' the ModChars to return $NUL and uncompress column 4 and
   ' to return column names.
   lsRows = slSelStr("Select * from Customers", "N4U4C")

   ' Display customer data going forward thru the string.  We
   ' will skip the first row cause it contains the column names.
   reset lsMsgData
   for llRowIX = 2 to parsecount(lsRows, $VT)
      lsRow = parse$(lsRows, $VT, llRowIX)

      ' Process each column.
      for llColIX = 1 to parsecount(lsRow, $BS)
         lsA = parse$(lsRow, $BS, llColIX)
         if llColIX = 4 then
            if lsA = $NUL then
               lsA = "NULL"
            else
               lsA = left$(lsA, 22)
            end if
         end if
         lsMsgData = lsMsgData & lsA & "     "
      next
      lsMsgData = lsMsgData & $CR
   next
   msgbox "Forward Thru String" & $CR & $CR & lsMsgData, , gsTitle

   ' Display customer data going backward thru the string.  We
   ' will show the column names as last row.
   reset lsMsgData
   for llRowIX = parsecount(lsRows, $VT) to 1 step -1
      lsRow = parse$(lsRows, $VT, llRowIX)

      ' Process each column.
      for llColIX = 1 to parsecount(lsRow, $BS)
         lsA = parse$(lsRow, $BS, llColIX)
         if llColIX = 4 then
            if lsA = $NUL then
               lsA = "NULL"
            else
               lsA = left$(lsA, 22)
            end if
         end if
         lsMsgData = lsMsgData & lsA & "     "
      next
      lsMsgData = lsMsgData & $CR
   next
   msgbox "Backward Thru String With Column Names" & $CR & $CR & lsMsgData, , gsTitle

End Sub

'==========================<[ Query Array ]>===========================
Sub QueryArray
' Will select all the columns for all the customers returning the record
' set in a 2 dimension array.   Once we have the array we will display
' the data going forward thru the array and then display the same data
' going backward.

   Local llColIX, llRowIX as Long
   Dim lsaColsRows(0) as String
   Local lsA as String
   Local lsMsgData as String

   ' ==========>>> SQLitening -- Sel Ary
   ' Get all columns for all customers into array.  Note
   ' the ModChars to return $NUL and uncompress column 4.
   slSelAry "Select * from Customers", lsaColsRows(), "N4U4"

   ' Display customer data going forward thru array.  We
   ' will skip row 0 cause that contains the column names.
   reset lsMsgData
   for llRowIX = 1 to ubound(lsaColsRows(2))

      ' Process each column.
      for llColIX = 1 to ubound(lsaColsRows(1))
         lsA = lsaColsRows(llColIX, llRowIX)
         if llColIX = 4 then
            if lsA = $NUL then
               lsA = "NULL"
            else
               lsA = left$(lsA, 22)
            end if
         end if
         lsMsgData = lsMsgData & lsA & "     "
      next
      lsMsgData = lsMsgData & $CR
   next
   msgbox "Forward Thru Array" & $CR & $CR & lsMsgData, , gsTitle

   ' Display customer data going backward thru array.  We
   ' will show the column names as last row.
   reset lsMsgData
   for llRowIX = ubound(lsaColsRows(2)) to 0 step -1

      ' Process each column.
      for llColIX = 1 to ubound(lsaColsRows(1))
         lsA = lsaColsRows(llColIX, llRowIX)
         if llColIX = 4 then
            if lsA = $NUL then
               lsA = "NULL"
            else
               lsA = left$(lsA, 22)
            end if
         end if
         lsMsgData = lsMsgData & lsA & "     "
      next
      lsMsgData = lsMsgData & $CR
   next
   msgbox "Backward Thru Array With Column Names" & $CR & $CR & lsMsgData, , gsTitle

End Sub

'=========================<[ Bind And Query ]>=========================
Sub BindAndQuery
' The boss has decided to store a memo in the customer table. This memo
' can be very long and is confidential so we must compress and encrypt.
' This example will use the Alter Table and Bind statements.

' The syntax and doc for slExeBind is:
'  slExeBind  (rsStatement String, rsBindDats String, [rsModChars String]) Long
'     Statement contains one (no multiples like slExe) Insert or Update
'     statement.  Will replace all of the '?' expressions in Statement with the
'     corresponding BindDat entry passed in BindDats.  A BindDat entry is the
'     specially formated string returned by the slBuildBindDat function.  Pass
'     BindDats as multiply concatenated BindDat entries to handle multiple '?'
'     expressions.  The first '?' will be replaced with the first BindDat entry,
'     the second '?' with the second BindDat entry, etc.  Excess '?' expressions
'     will be set to Null while excess BindDat entries will rais an error.  The
'     following example will insert three columns as follows:
'       ColName1 = Blob, ColName2 = Compressed Text, ColName3 = Compressed Encrypted Blob.
'         slExeBind slBuildInsertOrUpdate("TableName", _
'                                         "?" & $NUL & "?" & $NUL & "?" & $NUL, _
'                                          "ColName1, ColName2, ColName3"), _
'                                          slBuildBindDat("This is some Blob data") & _
'                                          slBuildBindDat("This is some copressed Text", "TC") & _
'                                          slBuildBindDat("This is a copressed and encrypted Blob", "CE")
'     ModChars:
'        Em = Return errors. This will override the global return errors flag.
'             m is the optional message display modifier and can be:
'                0 = No message is displayed.  This is the default.
'                1 = Display a warning message with OK button.  Error is
'                    returned when OK pressed.
'                2 = Display a question message with OK and Cancel buttons.
'                    If they press OK, error is returned.  If they press
'                    Cancel, will exit process.
'        e  = Do not return errors, display message and exit process. This
'             will override the global return errors flag.
'     Returns zero if processed OK. Else, depending on ModChars and the global
'     return errors flag, will either display error and exit or will return
'     the error number.

' The syntax and doc for slBuildBindDat is:
'  slBuildBindDat             (rsData String, [rsModChars String]) String
'     Returns a BindDat entry which is a specially formatted string required
'     by slExeBind.  Data contains the value you want converted into a BindDat.
'     A BindDat(s) is required to be passed to slExeBind.  The returned data
'     may also be compressed and/or encrypted.  If an error occurs then the
'     return value will be an empty string.  Use slGetError or slGetErrorNumber
'     to determine the error.
'     ModChars:
'        B = Bind as Blob.  This is the default.
'        C = Compress the data.
'        N = Encrypt the data.
'        T = Bind as Text.  Default is to bind as Blob.
'        Note: If both C and N then will first compress and then encrypt.

   Local llDo as Long
   Local lsMemo, lsMemoAgain as String


   ' Selected customer ID's
   Data  1, 3, 5

   ' Init memos
   lsMemo = "This is a sample memo. "
   lsMemo = lsMemo & repeat$(500, " Now is the time for all good men to come to the aid of their country. ")

   ' ==========>>> SQLitening -- Exe
   ' This will add a new column called Memo
   slExe "Alter Table Customers Add Memo"

   ' ==========>>> SQLitening -- Set Process Mods
   ' Set crypt key for subsequent encryption/decryption. x is the key which
   ' must be either 0, 16, 24, or 32 bytes.  If 0 bytes then will clear any
   ' existing key this is for security reasons.
   slSetProcessMods "KJustForTesting12"

   ' Compress and encrypt memo for each customer
   for llDo = 1 to datacount

      ' ==========>>> SQLitening -- Exe Bind
      ' You can bind data as either text or blob.  They both seem
      ' to work equally well. We will bind the memo data as text
      ' and compress and encrypt it.
      '       lsaData(1) = "q" & lsMemo
      slExeBind "Update Customers Set Memo = ? where CustomerID = " & read$(llDo), slBuildBindDat(lsMemo, "TCN")


   next

   ' ==========>>> SQLitening -- Sel
   ' Just for fun let's get the memo field from first customer
   slSel "Select Memo From Customers where CustomerID = " & read$(1), %snCustomers
   ' ==========>>> SQLitening -- Get Row
   ' Get the row for this customer.
   if slGetRow(%snCustomers) then


      ' ==========>>> SQLitening -- Get Field
      ' Get the memo field data in its raw form (no uncompress nor decrypt)
      lsMemoAgain = slFN("Memo", %snCustomers)
      msgbox "Compressed and Encrypted (all is not shown -- msgbox quits on first $NUL)" & $CR & $CR & lsMemoAgain, , gsTitle
      msgbox "Compressed and encrypted is not very readable is it.   Its length is only " & format$(len(slFN("Memo", %snCustomers))) & " while the original length was " & format$(len(lsMemo)) & _
             ".   Now we will uncompress and decrypt and show again, but only the first 1000 characters.", , gsTitle

      ' ==========>>> SQLitening -- Get Field
      ' Get the memo field data but this time we will us the extended version
      ' and ask for uncompress and decrypt which is the "UD" ModChar.  The other
      ' possibilities are to only uncompress ("U" ModChar) or to only decrypt
      ' ("D" ModChar).  You must be sure to select the correct one based upon
      ' how you bound the data with slExeBind.
      lsMemoAgain = slFNX("Memo", "UD", %snCustomers)
      msgbox "First 1000 characters of memo field." & $CR & $CR & left$(lsMemoAgain, 1000), , gsTitle

      ' Let's make sure the data from the memo field is same as original
      if lsMemoAgain <> lsMemo then
         msgbox "Got a problem.  This should not happen", , gsTitle
      end if

      ' ==========>>> SQLitening -- Get Field
      ' We did not GetRow until end so must manually close the Customers set
      slCloseSet %snCustomers
   end if


   ' The following code will not be commented like above because it is
   ' a repeat.  Its purpose is to show that you how to compress only or
   ' encrypt only.  This will encrypt a Blob

   ' Encrypt memo for each customer
   for llDo = 1 to datacount
      slExeBind "Update Customers Set Memo = ? where CustomerID = " & read$(llDo), slBuildBindDat(lsMemo, "BN")
   next
   slSel "Select Memo From Customers where CustomerID = " & read$(1), %snCustomers
   if slGetRow(%snCustomers) then
      lsMemoAgain = slFN("Memo", %snCustomers)
      msgbox "Encrypted  (all is not shown -- msgbox quits on first $NUL)" & $CR & $CR & lsMemoAgain, , gsTitle
      msgbox "Encryped is not very readable either.   Its length is " & format$(len(slFN("Memo", %snCustomers))) & " which is slightly larger then the original length of " & format$(len(lsMemo)) & _
             " .   Now we will decrypt and show again, but only the first 1000 characters.", , gsTitle
      lsMemoAgain = slFNX("Memo", "D", %snCustomers)
      msgbox "First 1000 characters of memo field." & $CR & $CR & left$(lsMemoAgain, 1000), , gsTitle
      if lsMemoAgain <> lsMemo then
         msgbox "Got a problem.  This should not happen", , gsTitle
      end if
      slCloseSet %snCustomers
   end if

   ' Compress memo as textfor each customer
   for llDo = 1 to datacount
      slExeBind "Update Customers Set Memo = ? where CustomerID = " & read$(llDo), slBuildBindDat(lsMemo, "TC")
   next
   slSel "Select Memo From Customers where CustomerID = " & read$(1), %snCustomers
   if slGetRow(%snCustomers) then
      lsMemoAgain = slFN("Memo", %snCustomers)
      msgbox "Compressed (all is not shown -- msgbox quits on first $NUL) " & $CR & $CR & lsMemoAgain, , gsTitle
      msgbox "Compressed is not very readable either. Compression makes it unreadable for human eyes but not from hacker eyes.  Its length is " & format$(len(slFN("Memo", %snCustomers))) & " while the original length was " & format$(len(lsMemo)) & _
             ".   Now we will uncompress and show again, but only the first 1000 characters.", , gsTitle
      lsMemoAgain = slFNX("Memo", "U", %snCustomers)
      msgbox "First 1000 characters of memo field." & $CR & $CR & left$(lsMemoAgain, 1000), , gsTitle
      if lsMemoAgain <> lsMemo then
         msgbox "Got a problem.  This should not happen", , gsTitle
      end if
      slCloseSet %snCustomers
   end if

End Sub

'=====================<[ Move Orders To Archive ]>=====================
Sub MoveOrdersToArchive

   Local llDo as Long

   ' Selected order ID's to move
   Data  1, 3, 5

   for llDo = 1 to datacount

      ' ==========>>> SQLitening -- Exe
      ' 1. Begin transaction
      ' 2. Insert the order
      ' 3. Delete the order
      ' 4. Insert line items
      ' 5. Delete line items
      ' 5. End transaction
      slExe "Begin; " & _
            "Insert into Archive.Orders Select * from Orders where OrderID = " & read$(llDo) & ";" & _
            "Delete from Orders where OrderID = " & read$(llDo) & ";" & _
            "Insert into Archive.LineItems Select * from LineItems where OrderID = " & read$(llDo) & ";" & _
            "Delete from LineItems where OrderID = " & read$(llDo) & ";" & _
            "End"

   next

End Sub

'===================<[ Create Archive and Attach ]>====================
Sub CreateArchiveAndAttach

   ' ==========>>> SQLitening -- Push Database
   ' Will push (save) the main database handle and process mods to the
   ' internal stack.  The current database handle is forgotten but not
   ' closed.  A slPopDatabase is required before reusing this database.
   slPushDatabase

   ' ==========>>> SQLitening -- Open
   ' This will open the archive database.  We will include the "C" ModChar
   ' which will create the file if it does not already exist. The extension
   ' can be anything you like.  SQLite like .Db3 while I prefer .Sld -- your
   ' choice.
   slOpen "ExampleC_Archive.Sld", "C"

   ' ==========>>> SQLitening -- Exe
   ' Begin transaction
   slExe "Begin"

   ' ==========>>> SQLitening -- Exe
   ' Drop all the archive tables if they exist.  We do this because we want
   ' to start with empty tables each time this example is run.  Note that
   ' we do all the drops in one slExe command
   slExe "Drop Table If Exists Orders; Drop Table If Exists LineItems"

   '
   ' ==========>>> SQLitening -- Exe
   ' Create the Orders table.  Note that this is the same as the table in main.
   slExe "Create Table Orders (OrderID Integer Primary Key Autoincrement, CustomerID, Date)"

   ' ==========>>> SQLitening -- Exe
   ' Create the LineItem table.  Note that this is the same as the table in main.
   slExe "Create Table LineItems (OrderID, PartID, Quantity, ExtendedPrice, Unique(OrderID, PartID))"

   ' ==========>>> SQLitening -- Exe
   ' End transaction
   slExe "End"

   ' ==========>>> SQLitening -- Close and Pop Database
   ' Will close the archive database and pop(restore) the main database
   ' handle and process mods from the internal stack.  We need to close
   ' because we may want to delete this database at program end.
   slClose
   slPopDatabase

   ' ==========>>> SQLitening -- Attach
   ' Attach the archive database as Archive
   slAttach "ExampleC_Archive.Sld", "Archive"

End Sub

'========================<[ Update Stuff #1 ]>=========================
Sub UpdateStuff1

   Local llFreePartID as Long

   ' ==========>>>
   ' The company just created a new product so we want to gave
   ' one to each customer that currently has an order.
   ' ==========>>> SQLitening -- Exe
   ' Add the new part to Parts table.  Include the "C" ModChar so we are
   ' sure it added.
   slExe "Insert into Parts (Description, Price) values ('Free Product', 0)", "C"

   ' ==========>>> SQLitening -- Get Insert ID
   ' Save the new PartID
   llFreePartID = slGetInsertID

   ' ==========>>> SQLitening -- Set Rel Name Locks
   ' Set a named lock
   slSetRelNamedLocks "+UpdateOrders"

   ' ==========>>> SQLitening -- Sel
   ' Select one order for each customer that currently has orders.
   ' The group by will do that.
   slSel "Select OrderID From Orders Group By CustomerID", %snOrders

   ' ==========>>> SQLitening -- Get Row
   ' Get all order rows.  %snOrders will automatically close.
   do while slGetRow(%snOrders)

      ' ==========>>> SQLitening -- Exe and BuildInsertOrUpdate
      ' Insert the free item into each order.  We use the BuildInsertOrUpdate, could
      ' also have used....
      '     "Insert into LineItems values(" & slFN("OrderID", %snOrders) & "," format$(llFreePartID) & ", 1, 0"
      slExe slBuildInsertOrUpdate("LineItems", slFN("OrderID", %snOrders) & $NUL & format$(llFreePartID) & $NUL & "1" & $NUL & "0")

   loop

   ' ==========>>> SQLitening -- Set Rel Name Locks
   ' Relesse all locks
   slSetRelNamedLocks "", "R"

   ' ==========>>>
   ' The owner decided to give one free muffler to each customer that has currently
   ' one or more on order.  He said to reduce the extended price of each muffler
   ' line item by the current price of a muffler.

   ' ==========>>> SQLitening -- Sel and BuildInsertOrUpdate
   ' Select the current muffler price.  Set number zero will be created because
   ' I omitted the set parm.  This is good for tempory sets.  Saves some
   ' coding time.

   slSel "Select Price From Parts where PartID = 4"
   if slGetRow then

      ' ==========>>> SQLitening -- Exe
      ' Update the extend price in all muffler line items
      slExe "Update LineItems set ExtendedPrice = ExtendedPrice - " & slFN("Price") & " where PartID = 4"

      ' ==========>>> SQLitening -- Close Set
      ' We did not GetRow until end so must manually close set zero
      slCloseSet

   else

      ' Can not find the muffler part
      msgbox "Trouble!!!!", , gsTitle

   end if

End Sub

'=========================<[ Query Stuff #1 ]>=========================
Sub QueryStuff1

   Local llDo as Long
   Local lsMessage as String
   Local lsPriorCustomer as String

   ' Selected customer ID's to test with
   Data  1, 2, 3, 4, 5, 6

   ' ==========>>>
   ' Show all orders for selected customers not using join.  This is an example
   ' of nested selects.  Two sets are active at same time (%snCustomers and %snOrders).
   ' This is similar to how your would code Betrive or ISAM.  This is OK if
   ' running in local mode but is a lot slower when running remote due to the
   ' number of extra selects being sent to server.  Join is the better way.
   reset lsMessage
   for llDo = 1 to datacount

      ' ==========>>> SQLitening -- Sel
      ' Select the name of the customer we will be showing order for.
      ' This will create the %snCustomers set
      slSel "Select CustomerID, Name from Customers where CustomerID = " & read$(llDo), %snCustomers

      ' ==========>>> SQLitening -- Get Row
      ' Get the row for this customer.
      if slGetRow(%snCustomers) then
         lsMessage = lsMessage & "Customer """ & slFN("Name", %snCustomers) & """ has following orders:" & $CR

         ' ==========>>> SQLitening -- Sel
         ' Select all orders for this customer creating the %snOrders set
         slSel "Select OrderID, Date from Orders where CustomerID = " & slFN("CustomerID", %snCustomers), %snOrders

         ' ==========>>> SQLitening -- Get Row
         ' Get all orders.  %snOrders set will be automatically closed
         do while slGetRow(%snOrders)
            lsMessage = lsMessage & "Order = " & slFN("OrderID", %snOrders) & "  Date = " & slFN("Date", %snOrders) & $CR
         loop

         ' ==========>>> SQLitening -- Close Set
         ' We did not GetRow on the %snCustomers set until end, which would have
         ' automatically closed the set, so we must manually close it now.
         slCloseSet %snCustomers

      else

         msgbox "Invalid customer ID " & read$(llDo), , gsTitle

      end if
   next
   msgbox "All orders for selected customers -  No Join" & $CR & $CR & lsMessage, , gsTitle



   ' ==========>>>
   ' Show all orders for selected customers using join.  This is an example
   ' of joining two tables.  Only one set is active (%snJoin). We use the
   ' left join so customers without orders will be returned.  This is much
   ' faster if running remote.
   reset lsMessage, lsPriorCustomer
   for llDo = 1 to datacount

      ' ==========>>> SQLitening -- Sel
      ' Select the customer and all its orders.  Creates the %snJoin set.
      slSel "Select Customers.CustomerID As Customer, Name, OrderID, Date " & _
            "From Customers Left Join Orders On Customers.CustomerID = Orders.CustomerID " & _
            "Where Customer = " & read$(llDo), %snJoin

      ' ==========>>> SQLitening -- Get Row
      ' Gets the first row of the %snJoin set.
      if slGetRow(%snJoin) then

         do
            if lsPriorCustomer <> slFN("Customer", %snJoin) then
               lsPriorCustomer = slFN("Customer", %snJoin)
               lsMessage = lsMessage & "Customer """ & slFN("Name", %snJoin) & """ has following orders:" & $CR
            end if
            if len(slFN("OrderID", %snJoin)) then lsMessage = lsMessage & "Order = " & slFN("OrderID", %snJoin) & "  Date = " & slFN("Date", %snJoin) & $CR

         ' ==========>>> SQLitening -- Get Row
         ' Gets the second and all other rows in the %snJoin set.  When
         ' there are no more rows the set will be automatically closed.
         loop while slGetRow(%snJoin)

      else

         msgbox "Invalid customer ID " & read$(llDo), , gsTitle

      end if
   next
   msgbox "All orders for selected customers -  Using Join" & $CR & $CR & lsMessage, , gsTitle


   ' ==========>>>
   ' Show all parts selected customer has on order not using join.  This is an example
   ' of many nested selects.  Four sets are active at same time (%snCustomers, %snOrders,
   ' %snLineItems, and %snParts).  This is similar to how your would code Betrive or ISAM.
   ' This is even slow if running in local mode but is a really sloooooow when running
   ' remote due to many number of extra selects being sent to server.  Join is the only way.

   reset lsMessage
   for llDo = 1 to datacount

      ' ==========>>> SQLitening -- Sel
      ' Select the name of the customer we will be showing order for.
      ' This will create the %snCustomers set
      slSel "Select Name from Customers where CustomerID = " & read$(llDo), %snCustomers

      ' ==========>>> SQLitening -- Get Row
      ' Get the row for this customer.
      if slGetRow(%snCustomers) then
         lsMessage = lsMessage & "Customer """ & slFN("Name", %snCustomers) & """ has following parts on order:" & $CR

         ' ==========>>> SQLitening -- Sel
         ' Select all orders for this customer creating the %snOrders set
         slSel "Select OrderID from Orders where CustomerID = " & read$(llDo), %snOrders

         ' ==========>>> SQLitening -- Get Row
         ' Get all orders.  %snOrders set will be automatically closed
         do while slGetRow(%snOrders)

            ' ==========>>> SQLitening -- Sel
            ' Select all line items for this order creating the %snLineItems set
            slSel "Select PartID, Quantity from LineItems where OrderID = " & slFN("OrderID", %snOrders), %snLineItems

            ' ==========>>> SQLitening -- Get Row
            ' Get all line items.  %snLineItems set will be automatically closed
            do while slGetRow(%snLineItems)

               ' ==========>>> SQLitening -- Sel
               ' Select the part from this line item creating the %snParts set.
               slSel "Select Description from Parts where PartID = " & slFN("PartID", %snLineItems), %snParts

               ' ==========>>> SQLitening -- Get Row
               ' Get the row for this part.
               if slGetRow(%snParts) then
                  lsMessage = lsMessage & "Order = " & slFN("OrderID", %snOrders) & "   Part = " & slFN("Description", %snParts) & "  Quantity = " & slFN("Quantity", %snLineItems) & $CR

                  ' ==========>>> SQLitening -- Close Set
                  ' We did not GetRow on the %snParts set until end, which would have
                  ' automatically closed the set, so we must manually close it now.
                  slCloseSet %snParts

               end if

            loop

         loop

         ' ==========>>> SQLitening -- Close Set
         ' We did not GetRow on the %snCustomers set until end, which would have
         ' automatically closed the set, so we must manually close it now.
         slCloseSet %snCustomers

      else

         msgbox "Invalid customer ID " & read$(llDo), , gsTitle

      end if
   next
   msgbox "All parts for selected customers -  No Join" & $CR & $CR & lsMessage, , gsTitle


   ' ==========>>>
   ' Show all parts selected customer has on order using join.  This is an example
   ' of joining four tables.  Only one set is active (%snJoin). We use the
   ' left join so customers without orders will be returned.  This is much, MUCH
   ' faster if running remote.  Note that we are also able to sort the returining
   ' rows.  Could not do that in the above no-join example.
   reset lsMessage
   for llDo = 1 to datacount

      ' ==========>>> SQLitening -- Sel
      ' Select the customer and all its orders, line items, and parts.  Creates the %snJoin set.
      slSel "Select Customers.CustomerID as Customer, Name, Orders.OrderID, Description, Quantity " & _
            "From Customers " & _
            "Left Join Orders On Customers.CustomerID = Orders.CustomerID " & _
            "Left Join LineItems On Orders.OrderID = LineItems.OrderID " & _
            "Left Join Parts On LineItems.PartID = Parts.PartID " & _
            "Where Customer = " & read$(llDo) & " " & _
            "Order By Orders.OrderID, Description", %snJoin

      ' ==========>>> SQLitening -- Get Row
      ' Gets the first row of the %snJoin set.
      if slGetRow(%snJoin) then

         do
            if lsPriorCustomer <> slFN("Customer", %snJoin) then
               lsPriorCustomer = slFN("Customer", %snJoin)
               lsMessage = lsMessage & "Customer """ & slFN("Name", %snJoin) & """ has following parts on order:" & $CR
            end if
            if len(slFN("OrderID", %snJoin)) then lsMessage = lsMessage & "Order = " & slFN("OrderID", %snJoin) & "   Part = " & slFN("Description", %snJoin) & "  Quantity = " & slFN("Quantity", %snJoin) & $CR

         ' ==========>>> SQLitening -- Get Row
         ' Gets the second and all other rows in the %snJoin set.  When
         ' there are no more rows the set will be automatically closed.
         loop while slGetRow(%snJoin)

      else

         msgbox "Invalid customer ID " & read$(llDo), , gsTitle

      end if
   next
   msgbox "All parts for selected customers -  Using Join" & $CR & $CR & lsMessage, , gsTitle

End Sub

'=========================<[ Query Stuff #2 ]>=========================
Sub QueryStuff2

   Local lsMessage as String
   Local lsPriorCustomer as String

   ' ==========>>>
   ' Show all parts on order for all customers havine orders using join.
   ' This is an example of joining four tables.  Only one set is active (%snJoin).
   reset lsMessage

   ' ==========>>> SQLitening -- Sel
   ' Select the customer and all its orders, line items, and parts.  Creates the %snJoin set.
   slSel "Select Customers.CustomerID as Customer, Name, Orders.OrderID, Description, Quantity " & _
         "From Customers " & _
         "Join Orders On Customers.CustomerID = Orders.CustomerID " & _
         "Join LineItems On Orders.OrderID = LineItems.OrderID " & _
         "Join Parts On LineItems.PartID = Parts.PartID " & _
         "Order By Orders.OrderID, Description", %snJoin

   ' ==========>>> SQLitening -- Get Row
   ' Get all rows.  %snJoin set will be automatically closed
   do while slGetRow(%snJoin)
      if lsPriorCustomer <> slFN("Customer", %snJoin) then
         lsPriorCustomer = slFN("Customer", %snJoin)
         lsMessage = lsMessage & "Customer """ & slFN("Name", %snJoin) & """ has following parts on order:" & $CR
      end if
      if len(slFN("OrderID", %snJoin)) then lsMessage = lsMessage & "Order = " & slFN("OrderID", %snJoin) & "   Part = " & slFN("Description", %snJoin) & "  Quantity = " & slFN("Quantity", %snJoin) & $CR
   loop
   msgbox "All parts for customers having orders -  Using Join" & $CR & $CR & lsMessage, , gsTitle

End Sub

'=========================<[ Create Orders ]>==========================
Sub CreateOrders

   Local llDo as Long
   Local llRC as Long
   Local llOrderID as Long
   Local llSkipOrder as Long

   ' If first is C then it is a customer. Second is CustomerID and third is date.
   ' If first is P then is is a part. Second is PartID and third is quantity.
   Data C, 1, 2008-1-1
   Data P, 1, 10
   Data P, 3, 5
   Data P, 2, 15
   Data C, 2, 2008-2-2
   Data P, 5, 10
   Data P, 3, 5
   Data P, 1, 15
   Data C, 3, 2008-3-3
   Data P, 3, 10
   Data P, 1, 5
   Data P, 2, 15
   Data C, 3, 2008-4-4
   Data P, 1, 10
   Data P, 3, 5
   Data P, 2, 15
   Data C, 4, 2008-5-5
   Data P, 4, 10
   Data P, 3, 5
   Data P, 2, 15
   Data C, 4, 2008-6-6
   Data P, 4, 10
   Data P, 3, 5
   Data P, 2, 15
   Data C, 5, 2008-7-7
   Data P, 5, 10
   Data P, 3, 5
   Data P, 2, 15

   ' ==========>>> SQLitening -- Exe
   ' Begin transaction
   slExe "Begin"

   for llDo = 1 to datacount step 3

      if read$(llDo) = "C" then

         ' ==========>>> SQLitening -- Sel
         ' Need to verify the customer ID.  The select requires that we select
         ' a column so the literal 1.  Note the set number is an equate.  Very
         ' good way to keep track of set numbers.
         slSel "Select 1 from Customers where CustomerID = " & read$(llDo + 1), %snCustomers

         ' ==========>>> SQLitening -- GetRow
         ' We do not need any values from the row, we just want to know if it
         ' exists so we add the "C" ModChar so the set will be close for us.
         ' It just saves us the extra command to close it ourselve.
         if slGetRow(%snCustomers, "C") then

            ' Valid customer
            llSkipOrder = %False

            ' ==========>>> SQLitening -- Exe and BuildInsertOrUpdate
            ' The customer is valid so we can start an new order. Note that
            ' we are using the third parm of BuildInsertOrUpdate and that
            ' just for fun we put the date before the customer id.
            slExe slBuildInsertOrUpdate("Orders", _
                                        read$(llDo + 2) & $NUL & read$(llDo + 1), _
                                        "Date, CustomerID")

            ' ==========>>> SQLitening -- GetInsertID
            ' This will return the ID of the order we just inserted above.
            ' We will need the when we insert the line items.
            llOrderID =  slGetInsertID

         else

            ' Invalid customer
            msgbox "Invalid customer. Order is not added.", , gsTitle
            llSkipOrder = %True

         end if

      elseif isfalse llSkipOrder then

         ' ==========>>> SQLitening -- Sel
         ' Need to verify the part ID.  Same idea as verify customer above except
         ' here we do need a value from the part record, the price.
         slSel "Select Price from Parts where PartID = " & read$(llDo + 1), %snParts

         ' ==========>>> SQLitening -- GetRow
         ' Check if valid and if yes then we have the price available
         ' to use when we add the line item.  Can't use the "C"
         ' ModChar cause we want the set to remain open.
         if slGetRow(%snParts) then

            ' Valid part

            ' ==========>>> SQLitening -- Exe and BuildInsertOrUpdate
            ' The part is valid so we can add line item. Note that we
            ' are using the third parm of BuildInsertOrUpdate.   Note
            ' that we must handle error so we can catch the attempt to
            ' add then same part more than once to the same order. That
            ' will rerun error 19 (not unique).  We can also get some
            ' errors returned that we do not want to handle.
            llRC = slExe(slBuildInsertOrUpdate("LineItems", _
                                               read$(llDo + 1) & $NUL & format$(llOrderID) & $NUL & read$(llDo + 2) & $NUL & slFN("Price", %snParts) & " * " & read$(llDo + 2) & $VT, _
                                              "PartID, OrderID, Quantity, ExtendedPrice"), "E")

            if llRC then

               if llRC = 19 then
                  msgbox "Can not add part more than once to an order.", , gsTitle
               else

                  ' ==========>>> SQLitening -- Get Error
                  ' Returns last error number and description from SQLite3.Dll for the
                  ' current database.  This must be called before doing another SQlite
                  ' command.  See SQLite documentation for complete list.
                  msgbox  "Can not handle following error." & $CR & $CR & slGetError, , gsTitle
                  ExitProcess 8

               end if
            end if

            ' ==========>>> SQLitening -- Close Set
            ' Now we must close the parts set. A set remains open until
            ' all rows are gotten (slGetRow returns $False) or it is
            ' closed with the slCloseSet command.
            slCloseSet %snParts

         else

            ' Invalid part
            msgbox "Invalid part. Line itme is not added.", , gsTitle

         end if

      end if

   next

   ' ==========>>> SQLitening -- Exe
   ' End transaction
   slExe "End"

End Sub

'=====================<[ Insert Customers ]>======================
Sub InsertCustomers

   Local llDo as Long

   Data "Goodwill Supply", "Englewood"
   Data "Tractor Industries", "New York"
   Data "Power To Go", "Portland"
   Data "Jackson Holes", "Bosie"
   Data "ABC Stuff", "Little Rock"
   Data "Last Stop Station", "Ghost Town"

   ' ==========>>> SQLitening -- Exe
   ' Begin transaction
   slExe "Begin"

   for llDo = 1 to datacount step 2

      ' ==========>>> SQLitening -- Exe and BuildInsertOrUpdate
      ' Insert some customer records.  We will use the slBuildInsertOrUpdate command
      ' to build our insert statements.  The first parm is the table name. The second
      ' parm is the name and city.  The third parm is the column names for the values
      ' in the second parm.  The fourth parm is omitted because we want to build an
      ' insert statement.  The sequence of the values and column names is not importaint
      ' as long as the align with each other.  If we had omitted the third parm we would
      ' have gotton any error because we omitted the CustomerID value.
      slExe slBuildInsertOrUpdate("Customers", _
                                   read$(llDo) & $NUL & read$(llDo + 1), _
                                   "Name,City")

   next

   ' ==========>>> SQLitening -- Exe
   ' End transaction
   slExe "End"

End Sub

'==========================<[ Insert Parts ]>==========================
Sub InsertParts

   Local llDo as Long

   Data "Truck Tire", "387.50"
   Data "Rear Bumper", "259.23"
   Data "AM/FM Radio", "123.45"
   Data "Muffler", "83.68"
   Data "Seat Covers", "55.98"

   ' ==========>>> SQLitening -- Exe
   ' Begin transaction
   slExe "Begin"

   for llDo = 1 to datacount step 2

      ' ==========>>> SQLitening -- Exe and BuildInsertOrUpdate
      ' Insert some part records.  We will use the slBuildInsertOrUpdate command
      ' to build our insert statements.  The first parm is the table name. The second
      ' parm is the name and city.  This time we will omit the third parm therefore
      ' we must have a value in the second parm for each column in the table and they
      ' must be in the same sequence.  Since we want the engine to assige the PartID
      ' we will send a Null value.
      slExe slBuildInsertOrUpdate("Parts", _
                                  "Null" & $NUL & read$(llDo) & $NUL & read$(llDo + 1))

   next

   ' ==========>>> SQLitening -- Exe
   ' End transaction
   slExe "End"

End Sub

'======================<[ Create Main Database ]>======================
Sub CreateMainDatabase

   ' ==========>>> SQLitening -- Exe
   ' Begin transaction
   slExe "Begin"

   ' ==========>>> SQLitening -- Exe
   ' Drop all the main tables if they exist.  We do this because we want
   ' to start with empty tables each time this example is run.  Note that
   ' we do all the drops in one slExe command
   slExe "Drop Table If Exists Customers; Drop Table If Exists Parts;" & _
         "Drop Table If Exists Orders; Drop Table If Exists LineItems"

   '
   ' ==========>>> SQLitening -- Exe
   ' Create the Customers table.  Note that the CustomerID will be the same value
   ' as the built-in RowID but we do not want a new customer to get the same number
   ' as a deleted one so we add the Autoincrement keyword. We do not assign a type
   ' to Name and City. A great thing about SQLite is that the type of a value is
   ' associated with the value itself, not with the column in which the value is
   ' stored.
   slExe "Create Table Customers (CustomerID Integer Primary Key Autoincrement, Name, City)"

   ' ==========>>> SQLitening -- Exe
   ' Create the Parts table.  Same concept as Customers.
   slExe "Create Table Parts (PartID Integer Primary Key Autoincrement, Description, Price)"

   ' ==========>>> SQLitening -- Exe
   ' Create the Orders table.  Same concept as Customers.
   slExe "Create Table Orders (OrderID Integer Primary Key Autoincrement, CustomerID, Date)"

   ' ==========>>> SQLitening -- Exe
   ' Create the LineItem table.  Each line item will have RowID as its primary
   ' key.  We do not want the same part to be on an order more than once (may not
   ' be real world) therefore the unique index.
   slExe "Create Table LineItems (OrderID, PartID, Quantity, ExtendedPrice, Unique(OrderID, PartID))"

   ' ==========>>> SQLitening -- Exe
   ' End transaction
   slExe "End"

End Sub
'==============================<[ Main ]>==============================
Function PbMain()

   Local lsServer as String
   Local lsPort as String


   ' Ask if they want to run local or remote
   select case msgbox("Do you want to run Local or Remote mode?" & $CR & $CR & "Yes = Local             No = Remote", %MB_YESNOCANCEL, "SQLitening Example C")
   case %IDNO
      lsServer = "LocalHost"
      lsServer = inputbox$("Enter remote server name", gsTitle, lsServer)
      if isfalse len(lsServer) then exit function
      lsPort = "51234"
      lsPort = inputbox$("Enter remote port number", gsTitle, lsPort)
      if isfalse len(lsPort) then exit function
      gsTitle = "SQLitening Example C -- Remote Mode (" & lsServer & "  " & lsPort & ")"
   case %IDYES
      lsServer = $NUL
      gsTitle = "SQLitening Example C -- Local Mode"
   case %IDCANCEL
      exit function
   end select

   ' ==========>>> SQLitening -- Connect
   ' Will connect to a SQLitening server.  This is only required if
   ' you want to run in remote mode. Do not call this routine or set
   ' Server to $NUL if you want to run in local mode. If Server is
   ' omitted or empty it will default to LocalHost.
   slConnect lsServer, val(lsPort)

   ' ==========>>> SQLitening -- Open
   ' This will open the main database.  We will include the "C" ModChar
   ' which will create the file if it does not already exist. The extension
   ' can be anything you like.  SQLite likes .Db3 while I prefer .Sld -- your
   ' choice.
   slOpen "ExampleC_Main.Sld", "C"

   ' Create main database
   CreateMainDatabase

   ' Insert cusomers
   InsertCustomers

   ' Insert parts
   InsertParts

   ' Create orders
   CreateOrders

   ' Query some stuff
   QueryStuff1

   ' Update some stuff
   UpdateStuff1

   ' Query some stuff
   QueryStuff2

   ' Create archive and attach
   CreateArchiveAndAttach

   ' Move order to archive
   MoveOrdersToArchive

   ' Update and query using compress and encrypt with bind
   BindAndQuery

   ' Query using an array for the record set
   QueryArray

   ' Query using a string for the record set
   QueryString

   ' Copy database (badkup)
   CopyDatabase

   ' ==========>>> SQLitening -- Close
   ' Closes the current database handle and sets it to nothing.  This is
   ' optional.  All databases will be automatically closed when your program
   ' ends.  We need to close now because we may want to delete the databases.
   slClose

   ' Done
   if lsServer = $NUL then
      if msgbox("End of SQLitening Example C.  Do you want to delete the four databases used in this example?", %MB_YESNO, gsTitle) = %IDYES then
         kill "ExampleC_Main.Sld"
         kill "ExampleC_Archive.Sld"
         kill "ExampleC_Main.Bak"
         kill "ExampleC_Archive.Bak"
      end if
   else
      msgbox "End of SQLitening Example C.   The following four files were created in the same folder as the service which is running on your server.  You will want to delete them manually." & $CR & $CR & _
             "ExampleC_Main.Sld" & $cr & "ExampleC_Archive.Sld" & $cr & _
             "ExampleC_Main.Bak" & $cr & "ExampleC_Archive.Bak", ,gsTitle
   end if

End Function
